﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TT.HelpInfo;
using SQLHelper;
using Bill.BasDLL.ety;
using System.Data;

namespace Bill.BasDLL.dao
{
    public class daoOraBillPrice : basClassDeclare
    {
        public daoOraBillPrice(TT.Module.sysLogin.etyBaeSystemParamter basSysPara)
        {
            _sqlHelp = basSysPara.SqlHelp;
            sPortCode = basSysPara.PORT_CODE;
            sUserID = basSysPara.UserID;
        }

        public bool Insert(etyBillPrice eyTrk)
        {
            eyTrk.PORT_CODE = sPortCode;
            eyTrk.LST_USER = sUserID;
            string sql = "insert into bill_price (PORT_CODE,CASH_FLAG,CUSTOMER,PRICE_SEQ,ITEM,METHOD,UNIT,QTY,FROM_QTY,TO_QTY,QTY_PER,UNIT_PER,PRICE,PRICE_BAS,CHRG_FLT1,CHRG_FLT2,CHRG_FLT3,CHRG_FLT4,CHRG_FLT5,CHRG_FLT6,CHRG_FLT7,CHRG_FLT8,CHRG_FLT9,LST_MOD,LST_USER,LST_UPD_DATE,FAV_SEQ_ID,DISCOUNT,DISCOUNT_BAS,QTY_BAS) "
                + "values(:PORT_CODE,:CASH_FLAG,:CUSTOMER,(select nvl(max(price_seq)+1,1) from bill_price),:ITEM,:METHOD,:UNIT,:QTY,:FROM_QTY,:TO_QTY,:QTY_PER,(SELECT ITEM_UNIT FROM BILL_CODE WHERE PORT_CODE=:PORT_CODE AND ITEM=:ITEM),:PRICE,:PRICE_BAS,:CHRG_FLT1,:CHRG_FLT2,:CHRG_FLT3,:CHRG_FLT4,:CHRG_FLT5,:CHRG_FLT6,:CHRG_FLT7,:CHRG_FLT8,:CHRG_FLT9,:LST_MOD,:LST_USER,sysdate,:FAV_SEQ_ID,:DISCOUNT,:DISCOUNT_BAS,:QTY_BAS)";

            return _sqlHelp.ExecuteTrans(sql, eyTrk).Exec_Success;

        }

        public bool Update(etyBillPrice eyTrk)
        {
            eyTrk.PORT_CODE = sPortCode;
            eyTrk.LST_USER = sUserID;
            string sql = "update bill_price set customer=:customer , qty_bas=:qty_bas, method=:METHOD,UNIT=:UNIT,qty=:QTY,FROM_qty=:FROM_QTY,TO_qty=:TO_QTY,qty_per=:QTY_PER,price=:price,PRICE_BAS=:PRICE_BAS,CHRG_FLT1=:CHRG_FLT1,CHRG_FLT2=:CHRG_FLT2,CHRG_FLT3=:CHRG_FLT3,CHRG_FLT4=:CHRG_FLT4,CHRG_FLT5=:CHRG_FLT5,CHRG_FLT6=:CHRG_FLT6,CHRG_FLT7=:CHRG_FLT7,CHRG_FLT8=:CHRG_FLT8,CHRG_FLT9=:CHRG_FLT9,lst_mod=:LST_MOD,LST_USER=:LST_USER,lst_upd_date=sysdate,DISCOUNT=:DISCOUNT,DISCOUNT_BAS=:DISCOUNT_BAS,CASH_FLAG=:CASH_FLAG "
                 + " where port_code=:PORT_CODE and price_seq=:PRICE_SEQ ";

            return _sqlHelp.ExecuteTrans(sql, eyTrk).Exec_Success;

        }

        public bool Delete(etyBillPrice eyBillCode)
        {
            string sql = "delete from bill_price "
                + " where port_code=:port_code and price_seq=:price_seq ";

            return _sqlHelp.ExecuteTrans(sql, eyBillCode).Exec_Success;

        }

        public DataTable GetBillPrice(string favId,string client, string billCode)
        {
            string sql = string.Empty;
            //sql = "select p.customer,p.customer_name as client_name,p.ITEM,Bc.ITEM_DESC,p.QTY,p.FROM_QTY,P.TO_QTY,p.QTY_PER,p.UNIT_PER,p.METHOD,ROUND(p.PRICE,2) PRICE,p.DISCOUNT,round(P.PRICE_BAS,2) PRICE_BAS,p.DISCOUNT_BAS,"
            //    + "P.CHRG_FLT1 AS BUSS_TYPE,P.CHRG_DESC1 AS BUSS_TYPE_DESC,P.CHRG_FLT2 AS AREA,P.CHRG_FLT3 AS SF,p.CHRG_DESC3 AS SF_DESC,P.CHRG_FLT4 AS CITY,P.CHRG_DESC4 AS CITY_DESC,P.CHRG_FLT5 AS QX,P.CHRG_DESC5 AS QX_DESC,"
            //    + "p.FROM_DATE,p.TO_DATE,p.PRICE_SEQ,p.qty_bas "
            //    + " from VW_bill_price p"
            //    + " left join bill_code BC on p.port_code=Bc.port_code and p.item=Bc.item " 
            //    + " where p.port_code=:port_code "
            //    + " order by p.customer,p.item";
            sql = "select * from vw_bill_price p "
                + " where p.port_code=:port_code ";
            if (!string.IsNullOrEmpty(client))
                sql += " and p.customer='" + client + "' ";
            if (!string.IsNullOrEmpty(billCode) && billCode != "***")
                sql += " and p.ITEM='" + billCode + "' ";
            sql += " order by p.customer,p.item";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { sPortCode });
            return dt;
        }
        public DataTable GetClient()
        {
            string sql = string.Empty;
            sql = "select CLIENT_ID,CLIENT_NAME from bas_client "
                + " order by CLIENT_ID ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { });
            return dt;
        }

        public DataTable GetBillCode()
        {
            string sql = string.Empty;
            sql = "select ITEM,ITEM_DESC,ITEM_UNIT FROM BILL_CODE WHERE PORT_CODE=:PORT_CODE ORDER BY ITEM ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { sPortCode });
            return dt;
        }

        public DataTable GetArea(string clientId, string bussType)
        {
            string sql = string.Empty;
            sql = "select AREA_NAME FROM bas_area WHERE PORT_CODE=:PORT_CODE AND CLIENT_ID=:CLIENT_ID AND BUSS_TYPE=:BUSS_TYPE group by AREA_NAME ORDER BY AREA_NAME ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { sPortCode, clientId, bussType });
            return dt;
        }
    }
}
